fbpx
Logo.
Menu_mob. Mob_closed.

Data analysis
in Excel

Main image Data analysis in Excel.
240 min
€2150

Learn how to efficiently collect, transform and visualize data using specific tools and functions in Power Query and PowerPivot.

Expand your data analytics capabilities and increase your professional competence.

Join the course on data analysis in Excel course today and Unleash your potential!

For employees of which professions
knowledge of the course will be required

Data analysts

- This includes analysts who collect, process and analyze data to make management decisions.

Financial analysts

- is especially important for those who analyze financial data and make forecasts based on it.

IT specialists and
business data analysts

- for developers responsible for creating and maintaining databases and business reports.

Accountants, auditors

- Knowledge of preparing data for analysis using pivot tables will help you perform everyday tasks quickly and efficiently.

Marketing analysts

- for those who analyze marketing data and make strategic decisions based on it.

Project managers

- Knowledge of Power Query and PowerPivot will help project managers analyze data related to project execution, benchmarks, budgets, and resources.

Detailed content course

Data collection
  • Collecting data from multiple files based on the same template.
  • Import a sheet or table.
  • Timeframes of data conversion in Power Query.
  • Edit and transform columns.
  • Data types in columns.
  • Download the collected data to an Excel sheet.
  • Queries and connections panel in Excel.
  • Edit a request - add a new file using a template.
  • Import multiple sheets from one Excel workbook.
  • Working with filters in Power Query columns.
  • Extract data from tables.
  • Data indicator in columns.
  • Import data from different sheets of multiple files.
  • Create customizable columns.
  • Dealing with errors.
Data conversion
  • Select multiple columns from the list of imported data.
  • Edit, split, and merge columns.
  • Work with data filters.
  • Cancel column summarization.
  • Analyze the converted data in a pivot table (grouping, layout, changing the format of numbers, displaying with calculations).
  • Updating converted data when changing the dragnet.
Connecting and adding
  • Text filters.
  • Rounding of numerical data.
  • Add a date column and change the day of the month.
  • Filling in data down and up.
  • Delete rows.
  • Replace values.
  • Queries and connections - what's the difference?
  • Create a copy of the connection.
  • Edit a request or connection - change the source.
  • Add multiple connections to a single request.
  • Adding a new connection to an existing request.
  • Grouping requests and connections.
Data merging - part 1
  • Repeating: loading data from a file, the difference between loading a sheet and a table, connecting queries, handling errors.
  • How to turn a request into a connection.
  • Change the data type using localization.
  • Merge two queries by one column.
  • Merge types, result for different merge types.
Data merging - part 2
  • Get data from a table or range.
  • Repeat: Fill up or down, delete rows, filter by column value, cancel pivot, split a column by a delimiter, merge two columns, change the data type.
  • Create a conditional column.
  • How to check the correctness of data processing.
  • Text data in the source is a space or a new line.
  • Merge queries by multiple columns within a Query (without loading to Excel).
  • Repeat: create a custom column (calculate by formula), replace values, load data into Excel.
Specific methods of working in Power Query
  • Replacing the VLOOKUP function - combining two queries created from ranges.
  • Repeating: combining, replacing values, special column by formula, filling values down.
  • Columns summary command.
  • An analog of a pivot table with text values in the pivot area.
  • Features of working with month names in text format. grouping columns in Query.
  • Query formulas: get values from table rows.
  • Convert lists to text with a separator.
  • Import data from a text file.
  • Repeat: filter values, change data type using localization, remove errors, group columns.
Relational data model in PowerPivot
  • The concept of a relational data model in PowerPivot, creating a model from multiple tables.
  • Build pivot tables from multiple sources.
  • PowerPivot window, different views.
  • Create links in the diagram view.
  • Add a new table to an existing data model.
  • A date table is a necessary element of any data model.
Calculated columns and measures
  • Formatting columns with numbers in PowerPivot.
  • Creating calculated columns.
  • The concept of a column measure.
  • Creating your own measure by formula, including references to columns and measures in formulas.
  • Use of formulas: ROUND, FORMAT.
  • Sorting by column and how it affects the display of data in a pivot table.
  • Use of measures in building pivot tables.
  • Use separators to analyze pivot tables.
Hierarchy
  • The concept of field hierarchy in PowerPivot.
  • RELATED function (similar to the VLOOKUP function) to substitute values for a column from another table.
  • Use hierarchies when building pivot tables.
  • Features of working with fields included in the hierarchy.
Building KPIs
  • Review: Adding a table from an external source to the current data model, establishing relationships in the model, creating measures and using them in formulas, formatting columns and measures.
  • Peculiarity of working with measures used to build KPIs.
  • Targeted measure.
  • Indicators of plan implementation.
  • Using KPIs when building pivot tables.
  • A special feature of working with time intervals when analyzing plan implementation.
The CALCULATE() function
  • Counting unique values.
  • Calculate with conditions - the CALCULATE function.
  • Repeating: creating and formatting measures, using measures in pivot tables, displaying values with calculations, separators.
  • Ignoring delimiters in the CALCULATE function using the ALL function.
Functions of working with the date
  • Date functions as filters for the CALCULATE function.
  • Functions to limit the date from the beginning of the period to the current date: DATESMTD, MATESQTD, DATESYTD.
  • Conditional formatting: histograms as trend visualization.
  • Analyze data compared to the previous period: DATEADD function and time intervals in PowerPivot (example: year and month).
  • Handling errors in DAX formulas: using the IF function with logical conditions.
  • BLANK function to add an empty value.
Building dashboards
  • Dashboard: a sheet with several summary charts for complex data analysis.
  • Formatting charts: change the type, layout, add chart elements, format the axis and row of elements, change the value of the axis divisions, chart styles, and field buttons.
  • Data signatures with a separator.
  • A combination chart with an auxiliary axis to display data that differs significantly in size.
  • Adding a signature of only one point.
  • Add a separator to multiple charts at once.
  • Building a dashboard from charts that show the same data but with different filter values (for example, analyzing sales for a year in different regions).
Processing queries in PowerPivot with Power Query - part 1
  • Combining Power Query and PowerPivot: getting data from different sources and processing it using Query, transferring it to a data model in PowerPivot without saving it to Excel.
  • Recap: importing files from a folder, extracting data from tables, joining with a new query, types of joins.
  • Create a connection to load data into a PowerPivot model.
  • Recap: date table, creating relationships in a model, adding a new table from a file to an existing model, RELATED function, creating a measure, formatting, creating a pivot table, applying a default style, separator.
  • Features work with month names, sorting by column.
Processing queries in PowerPivot with Power Query - part 2
  • Repetition: CALCULATE function, date functions, creating a new measure by formula.
  • Features of using filters and separators by time periods (year, quarter, month, week) when building pivot tables.
  • Handling errors in DAX formulas: using the IF function with logical conditions.
  • Create a custom sort order with PowerPivot.
Final testing
  • 20 questions, passing score 70% (14 questions), the number of attempts is infinite, the time is not limited.

Demo video

Metodi training

The course is designed as a simulation of real-life Excel work, including interactive assignments so that participants can practice in conditions as close as possible to real-life work situations. Key learning methods include:

Interactive tasks

Tips for interactive tasks

Choosing the right answers

Entering values in cells

Recommendations
to start learning

To start the course on data analysis in Excel, participants should have:

Access to a computer with Microsoft Excel installed is also required for self-paced repetition of practical exercises and hands-on training.

Other Excel online course

Excel Basic Online Course

Excel course

Visualization in Excel

Common question.

Do I need to have basic Microsoft Excel skills to participate in this data analytics course?

To participate in the course on data analysis in Excel, it is recommended that you have certain basic skills: the basics of Excel, including working with data and basic functions, and desirable skills in creating formulas and working with pivot tables, which will facilitate the learning of the course material.

How does the course work?

The Excel course includes simulations, interactive exercises, and practical tasks that allow participants to learn at their own pace.

Will participants receive a certificate after completing the course?

Upon successful completion of the course, participants can receive an individual certificate. The conditions for obtaining a certificate (for example, the number of points scored, the material listened to, or the results of the final test) are determined by the customer.

Is there any access to the materials after the course?

The customer can independently customize the conditions of access to the materials after completing the course in accordance with the requirements of their company.

Is there a possibility of free familiarization with the course or its trial version?

A demo version of the course is available on this page. On request, you can also access individual elements of the course to adapt it to your specific needs.